package dbtLab3;

import java.util.ArrayList;
import java.sql.*;

/**
 * Database is a class that specifies the interface to the movie database. Uses
 * JDBC and the MySQL Connector/J driver.
 */
public class Database {
	/**
	 * The database connection.
	 */
	private Connection conn;

	/**
	 * An SQL statement object.
	 */
	private Statement stmt;

	/**
	 * Create the database interface object. Connection to the database is
	 * performed later.
	 */
	public Database() {
		conn = null;
	}

	/**
	 * Open a connection to the database, using the specified user name and
	 * password.
	 * 
	 * @param userName
	 *            The user name.
	 * @param password
	 *            The user's password.
	 * @return true if the connection succeeded, false if the supplied user name
	 *         and password were not recognized. Returns false also if the JDBC
	 *         driver isn't found.
	 */
	public boolean openConnection(String userName, String password) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(
					"jdbc:mysql://puccini.cs.lth.se/" + userName, userName,
					password);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * Close the connection to the database.
	 */
	public void closeConnection() {
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
		}
		conn = null;
	}

	/**
	 * Check if the connection to the database has been established
	 * 
	 * @return true if the connection has been established
	 */
	public boolean isConnected() {
		return conn != null;
	}

	public String getID(String id) {
		String s = null;
		try {
			PreparedStatement stmt = conn
					.prepareStatement("select name from users where name = ?");

			stmt.setString(1, id);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				s = rs.getString("name");
				System.out.println(s);
			}
		}

		catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				System.out.println("Close failade");
				e.printStackTrace();
			}
		}

		return s;

	}

	public ArrayList<String> getCookies() {

		ArrayList<String> movies = new ArrayList<String>();

		try {
			PreparedStatement stmt = conn
					.prepareStatement("select name from recipe");
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				movies.add(rs.getString("name"));
				// System.out.println(rs.getString("name"));
			}
		} catch (SQLException e) {
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				System.out.println("Close failade");
				e.printStackTrace();
			}
		}
		return movies;

	}

	public ArrayList<String> getPalletID(String recipeName) {

		ArrayList<String> palletIDs = new ArrayList<String>();

		try {
			PreparedStatement stmt = conn
					.prepareStatement("select idPallet from Pallet where Recipe_name = ?");
			stmt.setString(1, recipeName);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				palletIDs.add(rs.getString("idPallet"));
				// System.out.println(rs.getString("date"));
			}
		} catch (SQLException e) {

		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				System.out.println("Close failade");
				e.printStackTrace();
			}
		}
		
		return palletIDs;
	}

	public String getTheaName(String date) {
		String thea = null;
		try {
			PreparedStatement stmt = conn
					.prepareStatement("select theater_name from performance where date = ?");
			stmt.setString(1, date);
			ResultSet rs = stmt.executeQuery();
			rs.first();
			thea = rs.getString("theater_name");
			// System.out.println(rs.getString("theater_name"));

		} catch (SQLException e) {
		}
		return thea;
	}

	public int getSeats(String name, String date) {
		int seats = 0;
		try {
			PreparedStatement stmt = conn
					.prepareStatement("select free_seats from performance where theater_name = ? and date =?");
			stmt.setString(1, name);
			stmt.setString(2, date);
			ResultSet rs = stmt.executeQuery();
			rs.first();
			seats = rs.getInt("free_seats");
			System.out.println(rs.getInt("free_seats"));

		} catch (SQLException e) {
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				System.out.println("Close failade");
				e.printStackTrace();
			}
		}
		return seats;
	}

	public void updateSeats(String name, String date) {

		if (getSeats(name, date) <= 0) {
			System.out.println("NO MORE FREE SEATS FOR U M8!");
		} else {
			try {
				PreparedStatement stmt = conn
						.prepareStatement("update performance set free_seats = free_seats - 1 where theater_name = ? and date = ?");
				stmt.setString(1, name);
				stmt.setString(2, date);
				stmt.executeUpdate();
			} catch (SQLException e) {
			} finally {
				try {
					if (stmt != null) {
						stmt.close();
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					System.out.println("Close failade");
					e.printStackTrace();
				}
			}
		}
	}

	// Reservation add shit
	/* --- insert own code here --- */

	public void addReservation(String date, String movieName, String userName) {
		try {
			PreparedStatement stmt = conn
					.prepareStatement("insert into reservation(date, movie_name, user_name) value(?,?,?)");
			stmt.setString(1, date);
			stmt.setString(2, movieName);
			stmt.setString(3, userName);
			stmt.executeUpdate();
		} catch (SQLException e) {
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				System.out.println("Close failade");
				e.printStackTrace();
			}
		}
	}
}
